package PrjDBUtils

import (
	"database/sql"
	db_utils "dgo/goutils/db-utils"
	"dgo/goutils/utils"
	"fmt"
	"gitee.com/ymofen/supervalue"
	"strings"
	"time"
)

func RowsRange(rows *sql.Rows, rowRange func(idx int, row []db_utils.SqlValue, err error) bool) error {
	n := 0
	cols, err := rows.Columns()
	if err != nil {
		return err
	}
	fldcnt := len(cols)
	scanArgs := make([]interface{}, fldcnt)
	rowval := make([]db_utils.SqlValue, fldcnt)
	for i := 0; i < fldcnt; i++ {
		scanArgs[i] = &rowval[i].Value
	}

	for rows.Next() {
		if n > 0 {
			for i := 0; i < fldcnt; i++ {
				rowval[i].Value = nil
			}
		}
		err = rows.Scan(scanArgs...)
		n++
		if !rowRange(n, rowval, err) {
			break
		}
	}
	return err
}

func Row2JSON(json *wrapper.SuperValue, rowval []db_utils.SqlValue, lowkeyfields bool, cols []string) {
	for i := 0; i < len(cols); i++ {
		strKey := cols[i]
		if lowkeyfields {
			strKey = strings.ToLower(strKey)
		}
		realV := rowval[i].Value
		switch s := realV.(type) {
		case []byte:
			if len(s) == 1 {
				if s[0] == 0 {
					json.SetKeyBool(strKey, false)
					break
				} else if s[0] == 1 {
					json.SetKeyBool(strKey, true)
					break
				}
			}
			json.SetKeyString(strKey, string(s))
		case time.Time:
			json.SetKeyString(strKey, utils.DateTimeString3(s))
		case nil:
			break
		default:
			json.SetKeyValue(strKey, realV)
		}
	}
}

func RowsJSONRange(rows *sql.Rows, lowkeyfields bool, cb func(row *wrapper.SuperValue) bool) error {
	cols, err := rows.Columns()
	if err != nil {
		return err
	}
	for rows.Next() {
		rowValues, err := db_utils.GetRowValues(rows)
		if err == nil {
			json := wrapper.NewSVObject()
			Row2JSON(json, rowValues, lowkeyfields, cols)
			if !cb(json) {
				break
			}
		}
	}
	return nil
}

/*
sql 只能As一个
*/
func QueryFirstVal1(dao *db_utils.DBHelper, sql string) (val interface{}, err error) {
	rows, err := dao.Query(sql)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	v0, e0 := db_utils.GetRowFirstVal1(rows)
	if e0 != nil {
		err = e0
	} else {
		val = v0.AsVar("")
	}
	return val, nil
}

func QueryFirstVal(dao *db_utils.DBHelper, sql string) (val interface{}, err error) {
	rows, err := dao.Query(sql)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	if rows.Next() {
		rowval, e0 := db_utils.GetRowValues(rows)
		if e0 != nil {
			err = e0
			return
		}
		val = rowval[0].AsVar(nil)
	}

	return val, nil
}

func QuerySQL(dao *db_utils.DBHelper, sql string, data *wrapper.SuperValue, lowkeyfields bool) error {
	rows, err := dao.Query(sql)
	if err != nil {
		return err
	}
	defer rows.Close()
	return Rows2SV(rows, data, lowkeyfields)
}

func QuerySQLRange(dao *db_utils.DBHelper, sql string, rowRange func(idx int, row []db_utils.SqlValue, err error) bool) error {
	rows, err := dao.Query(sql)
	if err != nil {
		return err
	}
	defer rows.Close()
	return RowsRange(rows, rowRange)
}

func Rows2SV(rows *sql.Rows, data *wrapper.SuperValue, lowkeyfields bool) error {
	cols, err := rows.Columns()
	if err != nil {
		return err
	}
	var rec *wrapper.SuperValue

	fldcnt := len(cols)
	scanArgs := make([]interface{}, fldcnt)
	rowval := make([]db_utils.SqlValue, fldcnt)
	for i := 0; i < fldcnt; i++ {
		scanArgs[i] = &rowval[i].Value
	}

	n := 0
	for rows.Next() {
		if n > 0 {
			for i := 0; i < fldcnt; i++ {
				rowval[i].Value = nil
			}
		}
		err := rows.Scan(scanArgs...)
		if err != nil {
			return err
		}
		if data.IsArray() {
			rec = data.AppendObject()
		} else {
			rec = data
		}
		Row2JSON(rec, rowval, lowkeyfields, cols)
		n++
	}

	return nil
}

func UpdateInsOrUp(dao *db_utils.DBHelper, recUp *db_utils.RecUpdateUpbuilder, uptable, keyfields string) (upcnt int, err error) {
	lvsql := recUp.BuildExists("1", uptable, keyfields)
	if ok, _ := dao.Exists(lvsql); ok {
		lvsql = recUp.BuildUpdate(uptable, keyfields)
		res, err := dao.Execute(lvsql)
		if err != nil {
			return 0, err
		}
		n, _ := res.RowsAffected()
		return int(n), err
	} else {
		lvsql = recUp.BuildInsert(uptable)
		res, err := dao.Execute(lvsql)
		if err != nil {
			return 0, err
		}
		n, _ := res.RowsAffected()
		return int(n), err
	}
}

func Insert2DB(dao *db_utils.DBHelper, upData *wrapper.SuperValue, uptable string) error {
	cols, err := dao.GetColumns(uptable)
	if err != nil {
		return err
	}

	recUp := db_utils.NewRecUpdateUpbuilder()

	var uprecfn = func(rec *wrapper.SuperValue) error {
		recUp.Clear()
		rec.Range(func(key string, val *wrapper.SuperValue) bool {
			fieldname := key
			col := db_utils.FindColumnByName(cols, fieldname)
			if col == nil {
				return true
			}
			var newvalue string
			if val.IsObject() {
				nv := val.ValueByName("newValue")
				if nv == nil {
					nv = val.ValueByName("value")
				}
				newvalue = nv.String()
			} else {
				newvalue = val.String()
			}

			if col.DatabaseTypeName() == "GUID" && len(newvalue) == 0 {
				newvalue = "null"
			}

			if newvalue == "null" {
				recUp.SetValue(fieldname, nil)
			} else {
				recUp.SetValue(fieldname, newvalue)
			}
			return true
		})

		lvsql := recUp.BuildInsert(uptable)
		_, err := dao.Execute(lvsql)
		return err
	}

	if upData.IsArray() {
		upData.Range(func(key string, val *wrapper.SuperValue) bool {
			err = uprecfn(val)
			return err == nil
		})
	} else {
		return uprecfn(upData)
	}

	return err
}

/*
*

	{
		"RowState": "Modified",	"RowPriorState": "Unchanged",
		"Original": {
			"fid": "001",
			"fcode": "fcode",
			"fdesc": "fdesc",
			"fimage": "HEX"  // 16进制数据
		},
		"Current": {"fid": "002","fcode": "fcode","fdesc": "fdesc",	"fimage": "HEX" }
	}

	{
		"RowID": 1, "RowState": "Inserted",
		"Current": { "fid": "003",	"fcode": "xde"}
	}

	{
	  "RowID": 0,	"RowState": "Deleted", "RowPriorState": "Unchanged",
	  "Original": {"fid": "001","fcode":"fcode","fdesc": "fdesc"}
	}
*/
func UpdateDelta2DB(dao *db_utils.DBHelper, cols []*sql.ColumnType, recUp *db_utils.RecUpbuilderEx, upData *wrapper.SuperValue) (cnt int, err error) {
	state := upData.StringByName("RowState", "")
	if state == "Inserted" {
		cur := upData.ValueByName("Current")
		if cur == nil {
			return
		}
		cur.Range(func(key string, val *wrapper.SuperValue) bool {
			fieldname := key
			if db_utils.FindColumnByName(cols, fieldname) != nil {
				recUp.SetCurrent(fieldname, val.Variant())
			}
			return true
		})
		lvsql := recUp.BuildInsert()
		if len(lvsql) > 0 {
			n, err := dao.ExecuteEx(lvsql)
			if err != nil {
				return cnt, err
			}
			cnt += int(n)
		}
	} else if state == "Modified" {
		ori := upData.ValueByName("Original")
		if ori == nil {
			return
		}
		cur := upData.ValueByName("Current")
		if cur == nil {
			return
		}
		ori.Range(func(key string, val *wrapper.SuperValue) bool {
			fieldname := key
			if recUp.IsKeyField(fieldname) {
				recUp.SetOrignal(fieldname, val.Variant())
			}
			return true
		})
		cur.Range(func(key string, val *wrapper.SuperValue) bool {
			fieldname := key
			if db_utils.FindColumnByName(cols, fieldname) != nil {
				recUp.SetCurrent(fieldname, val.Variant())
			}
			return true
		})

		lvsql := recUp.BuildUpdate()
		if len(lvsql) > 0 {
			n, err := dao.ExecuteEx(lvsql)
			if err != nil {
				return cnt, err
			}
			cnt += int(n)
		}
	} else if state == "Deleted" {
		ori := upData.ValueByName("Original")
		if ori == nil {
			return
		}

		ori.Range(func(key string, val *wrapper.SuperValue) bool {
			fieldname := key
			if recUp.IsKeyField(fieldname) {
				recUp.SetOrignal(fieldname, val.Variant())
			}
			return true
		})

		lvsql := recUp.BuildDel()
		if len(lvsql) > 0 {
			n, err := dao.ExecuteEx(lvsql)
			if err != nil {
				return cnt, err
			}
			cnt += int(n)
		}
	}
	return
}

/*
*
keyfields: "a;b"

缺少where条件的记录不会被更新

{"key":"", "name":"", "code":"001"}
或者
[

	{"key":"xxx"},{}

]
*/
func Update2DB(dao *db_utils.DBHelper, upData *wrapper.SuperValue, uptable, keyfields string) (err error) {
	_, err = Update2DBEx(dao, upData, uptable, keyfields)
	return
}

func Update2DBEx(dao *db_utils.DBHelper, upData *wrapper.SuperValue, uptable, keyfields string) (cnt int, err error) {
	cols, err0 := dao.GetColumns(uptable)
	if err0 != nil {
		return 0, err0
	}

	recUp := db_utils.NewRecUpdateUpbuilder()

	var uprecfn = func(rec *wrapper.SuperValue) error {
		typ := rec.StringByName("__type", "")
		recUp.Clear()
		rec.Range(func(key string, val *wrapper.SuperValue) bool {
			fieldname := key
			col := db_utils.FindColumnByName(cols, fieldname)
			if col == nil {
				return true
			}
			var newvalue string
			if val.IsObject() {
				nv := val.ValueByName("newValue")
				if nv == nil {
					nv = val.ValueByName("value")
				}
				if nv != nil {
					newvalue = nv.String()
				} else {
					newvalue = val.String()
				}
			} else {
				newvalue = val.String()
			}

			if col.DatabaseTypeName() == "GUID" && len(newvalue) == 0 {
				newvalue = "null"
			}

			if newvalue == "null" {
				recUp.SetValue(fieldname, nil)
			} else {
				recUp.SetValue(fieldname, newvalue)
			}
			return true
		})

		if recUp.Count() == 0 {
			return nil
		}

		if typ == "update" {
			lvsql := recUp.BuildUpdate(uptable, keyfields)
			n, err := dao.ExecuteEx(lvsql)
			if err != nil {
				return err
			}
			cnt += int(n)
		} else if typ == "insert" {
			lvsql := recUp.BuildInsert(uptable)
			n, err := dao.ExecuteEx(lvsql)
			if err != nil {
				return err
			}
			cnt += int(n)
		} else if typ == "delete" {
			lvsql := recUp.BuildDelete(uptable, keyfields)
			n, err := dao.ExecuteEx(lvsql)
			if err != nil {
				return err
			}
			cnt += int(n)
		} else {
			wherestr := recUp.BuildWhere(keyfields)
			if len(wherestr) == 0 {
				lvsql := recUp.BuildInsert(uptable)
				_, err := dao.Execute(lvsql)
				if err != nil {
					return err
				}
			}
			lvsql := fmt.Sprintf("select 1 from %s where %s", uptable, wherestr)
			if ok, _ := dao.Exists(lvsql); ok {
				lvsql = recUp.BuildUpdate(uptable, keyfields)
				if len(lvsql) == 0 {
					return nil
				}
				n, err := dao.ExecuteEx(lvsql)
				if err != nil {
					return err
				}
				cnt += int(n)
			} else {
				lvsql = recUp.BuildInsert(uptable)
				n, err := dao.ExecuteEx(lvsql)
				if err != nil {
					return err
				}
				cnt += int(n)
			}
		}
		return nil

	}

	if upData.IsArray() {
		upData.Range(func(key string, val *wrapper.SuperValue) bool {
			err0 = uprecfn(val)
			return err0 == nil
		})
	} else {
		err0 = uprecfn(upData)
	}

	return
}
